﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Text;
using System.Xml;
using Jumpton.SFA.Server.BusinessRule.MasterData;
using Jumpton.SFA.Server.BusinessRule;
using Jumpton.SFA.Server.BusinessRule.System;
using Jumpton.SFA.Server.BusinessRule.InStoreDataMeasures;
using Jumpton.SFA.Server.Entity;
using Jumpton.Persistent;
using Jumpton.SFA.Server.BusinessRule.CallRoute;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using System.IO;
using NPOI.HPSF;
using System.Collections.Generic;
using Jumpton.SFA.Server.BusinessRule.CPR;
using NPOI.HSSF.Util;
using Jumpton.SFA.Entity;
using NPOI.SS.Util;
public partial class Print_ExportExcel : PageBase
{
    String ImagePath = System.Configuration.ConfigurationManager.AppSettings["ImagePath"];
    protected void Page_Load(object sender, EventArgs e)
    {


        if (!IsPostBack)
        {

            if (!String.IsNullOrEmpty(Request.QueryString["flag"]))
            {
                this.divVisitDetail.Visible = true;
                if (!String.IsNullOrEmpty(Request.QueryString["BeginTime"]))
                {
                    TxtStartDate.Text = BusinessLogic.GetDateTime(Request.QueryString["BeginTime"], "yyyyMMddHHmmss", "yyyy-MM-dd");
                    txtEndDate.Text = Convert.ToDateTime(BusinessLogic.GetDateTime(Request.QueryString["EndTime"], "yyyyMMddHHmmss", "yyyy-MM-dd")).AddDays(-1).ToString("yyyy-MM-dd");
                }
                if (!String.IsNullOrEmpty(Request.QueryString["user"]) && Request.QueryString["user"] != "0")
                {
                    this.lblName.Text = new PersonManager().LoadByUserID(Decimal.Parse(Request.QueryString["user"])).Name;
                }
            }

            GetTableCoum();

        }
    }

    //计算两个日期日期的时间差
    private string DateDiff(DateTime DateTime1, DateTime DateTime2)
    {
        string dateDiff = "";
        try
        {
            TimeSpan ts1 = new TimeSpan(DateTime1.Ticks);
            TimeSpan ts2 = new TimeSpan(DateTime2.Ticks);
            TimeSpan ts = ts1.Subtract(ts2).Duration();


            if (ts.Days != 0) dateDiff += ts.Days.ToString() + "天";
            if (ts.Hours != 0) dateDiff += ts.Hours.ToString() + "小时";
            if (ts.Minutes != 0) dateDiff += ts.Minutes.ToString() + "分钟";
            dateDiff += ts.Seconds.ToString() + "秒";

        }
        catch
        {

        }
        return dateDiff;
    }

    /// <summary>
    /// 从Table.XML配置文件取该表的所有字段
    /// </summary>
    /// <param name="TableName">节点的属性</param>
    public void GetTableCoum()
    {
        string TableName = Request.QueryString["Table"];
        ArrayList columns = new ArrayList();
        string sFile = Server.MapPath("Table.xml");
        XmlDocument doc = new XmlDocument();
        doc.Load(sFile);
        XmlNodeList list = doc.DocumentElement.SelectNodes("//Table [@Name='" + TableName + "']//Columns/Column");
        foreach (XmlNode col in list)
        {
            ListItem item = new ListItem(col.Attributes.GetNamedItem("HeaderText").Value,
                col.Attributes.GetNamedItem("Name").Value);
            chkDataTable.Items.Add(item);
        }

        //默认全选中
        for (int i = 0; i < chkDataTable.Items.Count; i++)
        {
            chkDataTable.Items[i].Selected = true;
        }

    }

    #region 循环得到checkboxlist的选中项
    /// <summary>
    /// 循环得到checkboxlist的选中项
    /// </summary>
    /// <param name="chkBox"></param>
    /// <param name="spie"></param>
    /// <returns></returns>
    public static string Getcheck(CheckBoxList chkBox, string spie)
    {
        StringBuilder selval = new System.Text.StringBuilder();
        for (int i = 0; i < chkBox.Items.Count; i++)
        {
            if (chkBox.Items[i].Selected)
            {
                selval.Append(chkBox.Items[i].Text + ",");
                //selval.Append(spie);
            }
        }
        selval.Remove(selval.Length - 1, 1);
        return selval.ToString();
    }
    #endregion

    #region

    /// <summary>
    /// 导出数据
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void txtExport_Click(object sender, EventArgs e)
    {
        Hashtable ht = new Hashtable();
        //绑定数据列
        foreach (ListItem item in chkDataTable.Items)
        {
            if (item.Selected)
            {
                BoundField field = new BoundField();
                field.DataField = item.Value;
                field.HeaderText = item.Text;
                ht.Add(item.Value.ToUpper(), item.Value);
            }
        }
        string excelname = "MVSData";
        object source = null;

        #region 获取数据库中的表名
        switch (Request.QueryString["Table"])
        {
            case "Brands":
                {
                    source = BrandManager.GetAllBrandsListDaynamic(Request.QueryString["Category"], Request.QueryString["Manu"], MyStateManager.DomainID, Request.QueryString["Valid"]).Tables[0];
                    break;
                }
            case "Products":
                {
                    //source = ProductManager.GetProductDynamic("",
                    //                                                Request.QueryString["Category"],
                    //                                                Request.QueryString["Brands"],
                    //                                                Request.QueryString["Manu"],
                    //                                                Request.QueryString["Form"],
                    //                                                MyStateManager.DomainID, Request.QueryString["Valid"], "1");
                    break;
                }
            case "Customers":
                {
                    System.Text.StringBuilder sb = new System.Text.StringBuilder(" AND C.DOMAIN_ID='" + this.MyStateManager.DomainID + "' ");
                    if (Request.QueryString["Org"] != string.Empty)
                    {
                        sb.Append(string.Format(" AND C.ORG_ID IN ({0})", OrgHidValue));
                    }
                    if (Request.QueryString["Customer"] != string.Empty)
                    {
                        sb.Append(" AND (C.[CODE] LIKE '%" + Request.QueryString["Customer"] + "%' or C.[NAME] LIKE '%" + Request.QueryString["Customer"] + "%') ");
                    }
                    if (Request.QueryString["Chain"] != string.Empty)
                    {
                        sb.Append(" And CHAINS.ID = '" + Request.QueryString["Chain"] + "' ");
                    }
                    if (Request.QueryString["Channel"] != string.Empty)
                    {
                        sb.Append(" AND LOCAL_LEVEL1_CODE =  '" + Request.QueryString["Channel"] + "'  ");
                    }
                    //if (Request.QueryString["Village"] != string.Empty)
                    //{
                    //    sb.Append(" AND C.REGION_ID =  '" + Request.QueryString["Village"] + "'  ");
                    //}
                    if (Request.QueryString["Status"] != string.Empty)
                    {
                        sb.Append(" AND C.STATUS =  '" + Request.QueryString["Status"] + "'  ");
                    }
                    if (Request.QueryString["Store_Level"] != string.Empty)
                    {
                        sb.Append(" AND C.BRACKET_ID =  '" + Request.QueryString["Store_Level"] + "'  ");
                    }
                    if (Request.QueryString["StartDate"] != string.Empty)
                    {
                        sb.Append(string.Format(" AND C.Last_Update >= '{0}'", Request.QueryString["StartDate"]));
                    }
                    if (Request.QueryString["EndDate"] != string.Empty)
                    {
                        sb.Append(string.Format(" AND C.Last_Update < '{0}'", DateTime.Parse(Request.QueryString["EndDate"]).AddDays(1)));
                    }

                    excelname = "CustomersList";
                    source = CustomerManager.SearchCustomer_excel(sb.ToString(), "1", Request.QueryString["Valid"], 0, 0, true).Tables[0];
                    break;
                }

            case "Customers_list":
                {
                    System.Text.StringBuilder sb = new System.Text.StringBuilder(" AND C.DOMAIN_ID=" + this.MyStateManager.DomainID + " ");
                    if (Request.QueryString["Org"] != string.Empty)
                    {
                        sb.Append(string.Format(" AND C.ORG_ID IN ({0})", OrgHidValue));
                    }
                    if (Request.QueryString["Customer"] != string.Empty)
                    {
                        sb.Append(" AND (C.[CODE] LIKE '%" + Request.QueryString["Customer"] + "%' or C.[NAME] LIKE '%" + Request.QueryString["Customer"] + "%') ");
                    }
                    if (Request.QueryString["Chain"] != string.Empty)
                    {
                        sb.Append(" And CHAINS.ID = '" + Request.QueryString["Chain"] + "' ");
                    }
                    if (Request.QueryString["Channel"] != string.Empty)
                    {
                        sb.Append(" AND LOCAL_LEVEL1_CODE =  '" + Request.QueryString["Channel"] + "'  ");
                    }
                    //if (Request.QueryString["Village"] != string.Empty)
                    //{
                    //    sb.Append(" AND C.REGION_ID =  '" + Request.QueryString["Village"] + "'  ");
                    //}
                    //if (Request.QueryString["Status"] != string.Empty)
                    //{
                    //    sb.Append(" AND C.STATUS =  '" + Request.QueryString["Status"] + "'  ");
                    //}
                    //if (Request.QueryString["Store_Level"] != string.Empty)
                    //{
                    //    sb.Append(" AND C.BRACKET_ID =  '" + Request.QueryString["Store_Level"] + "'  ");
                    //}
                    if (Request.QueryString["StartDate"] != string.Empty)
                    {
                        sb.Append(string.Format(" AND C.CREATE_DATE >= '{0}'", Request.QueryString["StartDate"]));
                    }
                    if (Request.QueryString["EndDate"] != string.Empty)
                    {
                        sb.Append(string.Format(" AND C.CREATE_DATE < '{0}'", DateTime.Parse(Request.QueryString["EndDate"]).AddDays(1)));
                    }

                    excelname = "CustomersList";
                    source = CustomerManager.SearchCustomer(sb.ToString(), "1", Request.QueryString["Valid"], 0, 0, true).Tables[0];
                    break;
                }
            case "Visits":
                {
                    System.Text.StringBuilder sb = new System.Text.StringBuilder();

                    if (Request.QueryString["org"] != string.Empty)
                        sb.Append(string.Format(" AND V.ORG_ID IN ({0})", Request.QueryString["org"]));

                    if (Request.QueryString["BeginTime"] != string.Empty)
                    {
                        sb.Append(" AND V.[START_TIME] >='" + Request.QueryString["BeginTime"] + "' ");
                    }
                    if (Request.QueryString["EndTime"] != string.Empty)
                    {
                        sb.Append(" AND V.[STOP_TIME] <='" + Request.QueryString["EndTime"] + "' ");
                    }
                    if (Request.QueryString["user"] != string.Empty)
                    {
                        sb.Append(" AND V.[PERSON_ID] IN (SELECT PERSON_ID FROM USERS WHERE USERS.ID='" + Request.QueryString["user"] + "') ");
                    }
                    if (Request.QueryString["name"] != string.Empty)
                    {
                        sb.Append(" AND (C.[CODE] LIKE '%" + Request.QueryString["name"] + "%' or C.[NAME] LIKE '%" + Request.QueryString["name"] + "%' or CI.[ADDRESS_LINE] like  '%" + Request.QueryString["name"] + "%' "
                        + " or C.default_ship_to_code LIKE '%" + Request.QueryString["name"] + "%' or  "
                        + " CI.telephone LIKE '%" + Request.QueryString["name"] + "%' or  CI.fax LIKE '%" + Request.QueryString["name"] + "%' "
                        + " )   ");
                    }

                    if (Request.QueryString["visitMode"].Length != 0 && Request.QueryString["visitType"].Length != 0)
                    {
                        sb.Append(string.Format(" AND V.VISIT_TYPE='{0}' ", Request.QueryString["visitType"] + Request.QueryString["visitMode"]));
                    }
                    else
                    {
                        if (Request.QueryString["visitMode"].Length == 0 && Request.QueryString["visitType"].Length > 0)
                        {
                            sb.Append(string.Format(@" AND SUBSTRING( V.VISIT_TYPE,1,1)='{0}' ", Request.QueryString["visitType"]));
                        }
                        else
                        {
                            if (Request.QueryString["visitMode"].Length > 0 && Request.QueryString["visitType"].Length == 0)
                            {
                                sb.Append(string.Format(@" AND SUBSTRING( V.VISIT_TYPE,2,1)='{0}' ", Request.QueryString["visitMode"]));
                            }
                        }
                    }

                    sb.Append("ORDER BY V.[STOP_TIME] DESC");

                    excelname = "VisitHistory";

                    DataTable dt = OrderHeaderManager.SearchHistoryCustomer_excel(sb.ToString()).Tables[0];

                    DataColumn column1 = new DataColumn("LAST_TIME", typeof(string));
                    dt.Columns.Add(column1);
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {

                        dt.Rows[i]["START_TIME"] = Jumpton.SFA.Server.BusinessRule.BusinessLogic.GetDateTime(dt.Rows[i]["START_TIME"].ToString(), "yyyyMMddHHmmss", "yyyy-MM-dd HH:mm:ss");
                        dt.Rows[i]["STOP_TIME"] = Jumpton.SFA.Server.BusinessRule.BusinessLogic.GetDateTime(dt.Rows[i]["STOP_TIME"].ToString(), "yyyyMMddHHmmss", "yyyy-MM-dd HH:mm:ss");
                        dt.Rows[i]["LAST_TIME"] = "";
                        try
                        {
                            dt.Rows[i]["LAST_TIME"] =
                            DateDiff(Convert.ToDateTime(dt.Rows[i]["START_TIME"]), Convert.ToDateTime(dt.Rows[i]["STOP_TIME"]));
                        }
                        catch (Exception)
                        {
                        }

                    }

                    source = dt;


                    break;

                }
            case "OrderReport":
                {
                    if (Session["ordercondition"] != null)
                    {
                        ViewState["ordercondition"] = Session["ordercondition"];
                        Session["ordercondition"] = null;
                    }
                    string where = "";
                    if (ViewState["ordercondition"] != null)
                        where = ViewState["ordercondition"].ToString();
                    DataSet ds = OrderHeaderManager.sp_SelectOrdersForExcel(MyStateManager.DomainID, where);
                    //DataSet ds = OrderHeaderManager.SearchSalesOrder(
                    //                                                Request.QueryString["Org"],
                    //                                                Request.QueryString["Chains"],
                    //                                                Request.QueryString["User"],
                    //                                                Request.QueryString["VisitDate"],
                    //                                                this.MyStateManager.DomainID
                    //                                             );
                    ds.Tables[0].Columns.Add("单价");
                    ds.Tables[0].Columns.Add("发货数量");
                    ds.Tables[0].Columns.Add("收货数量");
                    ds.Tables[0].Columns.Add("收货时间");
                    ds.Tables[0].Columns.Add("客户签收");
                    source = ds.Tables[0];

                    break;
                }
            case "UserCustomerList":
                {
                    int userid = 0;
                    if (int.TryParse(Request.QueryString["User"].ToString(), out userid))
                    {
                        excelname = "CustomerList" + DateTime.Now.ToShortDateString();
                        source = CustomerRouteManager.SelectUserCustomer(userid, this.MyStateManager.DomainID, this.MyStateManager.OrgID).Tables[0];
                    }
                    else
                    {
                        return;
                    }
                    break;

                }
            case "UserRouteList":
                {
                    int userid = 0;
                    if (int.TryParse(Request.QueryString["User"].ToString(), out userid))
                    {
                        excelname = "RouteList" + DateTime.Now.ToShortDateString();
                        source = CustomerRouteManager.SelectRouteList(userid, Request.QueryString["VALID"]).Tables[0];
                    }
                    else
                    {
                        return;
                    }
                    break;

                }
            case "SystemUserList":
                {
                    decimal groupID = 0;
                    if (decimal.TryParse(Request.QueryString["Group"].ToString(), out groupID))
                    {
                        source = UserManager.SelectUserDynamicForExcel(
                                                                Request.QueryString["LoginName"],
                                                                Request.QueryString["Name"],
                                                                Request.QueryString["Position"],
                                                                MyStateManager.DomainID,
                                                                OrgHidValue,
                                                                groupID
                                                                ).Tables[0];
                        excelname = "SystemUserList";
                    }
                    else
                    {
                        ShowMessage("由于角色类型传送失败导致无法导出Excel,请返回上级页面重新尝试");
                        return;
                    }
                    break;
                }
            case "PeopleList":
                {
                    try
                    {
                        source = PersonManager.SelectPERSONSsDynamicForExcel(
                                                                    Request.QueryString["Name"],
                                                                    Request.QueryString["Sex"],
                                                                    Request.QueryString["Position"],
                                                                    MyStateManager.DomainID,
                                                                    OrgHidValue,
                                                                    Request.QueryString["Valid"]
                                                                    )
                                                                    .Tables[0];
                        excelname = "PeopleList";


                    }
                    catch
                    {
                        ShowMessage("无法导出Excel,请返回上级页面重新尝试");
                        return;
                    }

                    break;
                }
            case "Persons":
                {

                    break;
                }
            case "VisitBaseInfo":
                {
                    int userid = 0;
                    if (int.TryParse(Request.QueryString["User"].ToString(), out userid) && !String.IsNullOrEmpty(Request.QueryString["BeginTime"]))
                    {
                        excelname = "基本信息" + DateTime.Now.ToShortDateString();
                        source = new VisitManager().GetExportVisitBaseInfo(userid, MyStateManager.DomainID, Request.QueryString["BeginTime"], Request.QueryString["EndTime"]);
                    }
                    else
                    {
                        return;
                    }
                    break;


                }

        }
        #endregion

        string filename = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";

        Response.ContentType = "application/vnd.ms-excel";
        Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
        Response.Clear();
        DataTable dtData = ((DataTable)source);
        InitializeWorkbook();
        GenerateData(filename, dtData);
        Response.BinaryWrite(WriteToStream().GetBuffer());
        Response.End();

    }


    //康师傅导出基本数据

    private void GenerateVisitBaseInfo()
    {
        //Int64 beginTime = Int64.Parse(Request.QueryString["BeginTime"]);
        //Int64 endTime = Int64.Parse(Request.QueryString["EndTime"]);
        DateTime beginTime = DateTime.Parse(this.TxtStartDate.Text);
        DateTime endTime = DateTime.Parse(this.txtEndDate.Text);

        Decimal userID = 0;
        try
        {
            userID = Decimal.Parse(Request.QueryString["user"].ToString());
        }
        catch
        {

        }

        DataTable dt_visits = new VisitManager().GetExportVisitBaseInfo(userID, MyStateManager.DomainID, beginTime.ToString("yyyyMMddHHmmss"), endTime.AddDays(1).ToString("yyyyMMddHHmmss"));
        if (dt_visits.Rows.Count == 0)
        {
            ScriptManager.RegisterStartupScript(this, this.GetType(), "", "alert('无数据')", true);
            return;
        }

        //初始化文件
        InitializeWorkbook();
        ISheet sheet1 = hssfworkbook.CreateSheet(DateTime.Now.ToString("yyyyMMddHHmmss"));//创建工作簿
        ICellStyle cellStyle = hssfworkbook.CreateCellStyle();//创建单元格样式
        IDataFormat format = hssfworkbook.CreateDataFormat();//创建显示格式
        IFont font = hssfworkbook.CreateFont();//创建字体
        font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;//加粗

        font.FontHeight = 300;
        cellStyle.SetFont(font);

        cellStyle.Alignment = HorizontalAlignment.CENTER;//居中
        cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
        cellStyle.FillForegroundColor = HSSFColor.SKY_BLUE.index;
        cellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
        cellStyle.BorderTop = cellStyle.BorderBottom = cellStyle.BorderLeft = cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;


        //内容行
        ICellStyle cellStyle1 = hssfworkbook.CreateCellStyle();//创建单元格样式
        cellStyle1.BorderTop = cellStyle1.BorderBottom = cellStyle1.BorderLeft = cellStyle1.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
        IFont font1 = hssfworkbook.CreateFont();//创建字体
        font1.FontHeight = 220;
        cellStyle1.SetFont(font1);
        cellStyle1.Alignment = HorizontalAlignment.CENTER;//居中
        cellStyle1.VerticalAlignment = VerticalAlignment.CENTER;

        //IRow row = sheet1.CreateRow(0);
        //sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 20));//合并
        //row.CreateCell(0).SetCellValue("表头测试");
        IRow row1 = sheet1.CreateRow(1);
        row1.Height = 500;
        ArrayList columns = new ArrayList();
        string sFile = Server.MapPath("Table.xml");
        XmlDocument doc = new XmlDocument();
        doc.Load(sFile);
        XmlNodeList xmllist = doc.DocumentElement.SelectNodes("//Table [@Name='VisitBaseInfo']//Columns/Column");
        string columnname = "";
        //设置列头
        int cindex = 0;
        MyTable[] MyTables = new MyTable[xmllist.Count];
        foreach (XmlNode col in xmllist)
        {
            columnname = col.Attributes.GetNamedItem("HeaderText").Value;
            ICell c = row1.CreateCell(cindex);
            string HeaderText = col.Attributes.GetNamedItem("HeaderText").Value;
            c.SetCellValue(HeaderText);
            MyTables[cindex] = new MyTable();
            MyTables[cindex].AutoMerged = col.Attributes.GetNamedItem("AutoMerged").Value;//保存是否合并
            MyTables[cindex].Width = col.Attributes.GetNamedItem("Width").Value;//列宽
            MyTables[cindex].CellType = col.Attributes.GetNamedItem("Type").Value;//单元格类型
            sheet1.SetColumnWidth(cindex, int.Parse(MyTables[cindex].Width) * 256);
            c.CellStyle = cellStyle;
            cindex++;
        }


        int rowIndexFlag = 2;
        List<DataRow> list = new List<DataRow>();
        var query =
                  from temp in dt_visits.AsEnumerable().Select(s => s.Field<Decimal>("VISITID")).Distinct()
                  select temp;
        if (query.Count<Decimal>() > 0)
        {
            Decimal[] dt = query.ToArray<Decimal>();
            for (int i = 0; i < dt.Length; i++)
            {

                DataTable tempDT = dt_visits.AsEnumerable().Where(w => w.Field<Decimal>("VISITID") == dt[i]).CopyToDataTable();

                GenerateRow(tempDT, sheet1, cellStyle1, rowIndexFlag, out rowIndexFlag);

            }
        }


        //定义输出类型
        HttpContext.Current.Response.Charset = "UTF-8";
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
        string filenameDetail = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
        string filename = HttpUtility.UrlEncode(filenameDetail, System.Text.Encoding.UTF8);
        Response.ContentType = "application/vnd.ms-excel";
        Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
        Response.Clear();
        Response.BinaryWrite(WriteToStream().GetBuffer());
        Response.End();
    }


    public List<String> GetListStr(String str, String[] compareStr)
    {
        String[] sourceBags = str.Split(';');
        List<String> list = new List<string>();
        foreach (var source in sourceBags)
        {
            foreach (var compare in compareStr)
            {
                String[] s = source.Split(',');
                if (compare.Equals(s[0]))
                {
                    list.Add(compare);
                    break;
                }
            }
        }
        return list;
    }
    public String GetStringStr(String str, String[] compareStr)
    {
        String[] sourceBags = str.Split(';');
        String result = "";
        foreach (var source in sourceBags)
        {
            foreach (var compare in compareStr)
            {
                String[] s = source.Split(',');
                if (compare.Equals(s[0]))
                {
                    result += compare + ";";
                    break;
                }
            }
        }
        if (result.Length > 1)
            result = result.Substring(0, result.Length - 1);
        return result;
    }

    //获取容器面的数组
    //生成行
    private void GenerateRow(DataTable dt, ISheet sheet1, ICellStyle cellStyle1, int rowIndex, out int rowIndexFlag)
    {
        //1.list的条数，需要合并的行   rowIndex1
        //2.在单条visit数据中，根据BAG_NOODLE\CONTAINER_NOODLE，区分是袋面还是容器面
        //3.判断袋面和容器面的条数，计算需要合并的行 rowIndex2
        int indexFlag = rowIndex;
        for (int i = 0; i < dt.Rows.Count; i++)
        {

            //五连包,12.5;三连桶,3.8   五连包\r;盒面\r
            String str = dt.Rows[i]["BAG_NOODLE"].ToString().Replace("\r", "");
            //List<String> listBags = GetListStr(str, new String[] { "五连包", "单包" });
            //List<String> listContains = GetListStr(str, new String[] { "三连桶", "单桶", "盒面", "三联杯", "杯面" });
            String listBags = GetStringStr(str, new String[] { "五连包", "单包" });
            String listContains = GetStringStr(str, new String[] { "三连桶", "单桶", "盒面", "三联杯", "杯面" });
            //rowIndex += listContains.Count - listBags.Count > 0 ? listContains.Count : listBags.Count;

            IRow row = sheet1.CreateRow(indexFlag);
            row.Height = 400;
            //int cellFlag = 0;
            for (int k = 0; k < dt.Rows[i].ItemArray.Length - 4; k++)
            {
                //创建单元格、合并
                ICell cell = row.CreateCell(k);
                ////合并
                //if (k != 4 && k != 5 && k != 6 && k != 7)
                //{
                //    //合并条件为数据大于1行
                //    if (dt.Rows.Count - i != 1)
                //    {
                //        sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(indexFlag, indexFlag + 1, k, k));//合并
                //    }

                //}


                // 推广场次
                if (k == 8)
                {

                    cell.SetCellValue(dt.Rows.Count);
                }
                //时间转换
                else if (k == 3)
                {
                    String s = "";
                    try
                    {
                        s = BusinessLogic.GetDateTime(dt.Rows[i][k].ToString(), "yyyyMMddHHmmss", "yyyy-MM-dd HH:mm");
                    }
                    catch
                    {


                    }
                    cell.SetCellValue(s);

                }
                //渠道
                else if (k == 2)
                {
                    String s = "";
                    try
                    {
                        s = new DictionaryItemsManager().Load(Convert.ToInt32(dt.Rows[i][k])).Name;

                    }
                    catch
                    {

                    }
                    cell.SetCellValue(s);

                }

                else
                    cell.SetCellValue(dt.Rows[i][k].ToString());

                cell.CellStyle = cellStyle1;

            }

            indexFlag++;

        }
        rowIndexFlag = indexFlag;

    }


    private void GenerateRow_(DataTable dt, ISheet sheet1, ICellStyle cellStyle1, int rowIndex, out int rowIndexFlag)
    {
        //1.list的条数，需要合并的行   rowIndex1
        //2.在单条visit数据中，根据BAG_NOODLE\CONTAINER_NOODLE，区分是袋面还是容器面
        //3.判断袋面和容器面的条数，计算需要合并的行 rowIndex2
        int indexFlag = rowIndex;
        for (int i = 0; i < dt.Rows.Count; i++)
        {

            //五连包,12.5;三连桶,3.8   五连包\r;盒面\r
            String str = dt.Rows[i]["BAG_NOODLE"].ToString().Replace("\r", "");
            List<string> listBags = GetListStr(str, new String[] { "五连包", "单包" });
            List<string> listContains = GetListStr(str, new String[] { "三连桶", "单桶", "盒面", "三联杯", "杯面" });
            //累加行数
            int addLength = listContains.Count - listBags.Count > 0 ? listContains.Count : listBags.Count;

            //计算单个品牌数据需要的行数
            int cloRowCount = 1;
            if (addLength > 1)
            {

                cloRowCount = addLength;
            }


            for (int l = 0; l < cloRowCount; l++)
            {
                IRow row = sheet1.CreateRow(indexFlag);
                row.Height = 400;
                int cellFlag = 0;

                //合并
                if (cloRowCount > 1)
                {
                    if (cellFlag != 10 && cellFlag != 12 && cellFlag != 4 && cellFlag != 5 && cellFlag != 6 && cellFlag != 7)
                    {
                        //合并条件为数据大于1行
                        if (dt.Rows.Count - i != 1)
                        {
                            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(indexFlag, indexFlag + 1, cellFlag, cellFlag));//合并
                        }

                    }
                }

                for (int k = 3; k < dt.Rows[i].ItemArray.Length; k++)
                {
                    //创建单元格、合并
                    ICell cell = row.CreateCell(cellFlag);
                    //合并
                    if (cellFlag == 4 || cellFlag == 5 || cellFlag == 6 || cellFlag == 7)
                    {
                        //合并条件为数据大于1行
                        if (dt.Rows.Count - i != 1)
                        {
                            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(indexFlag, indexFlag + 1, cellFlag, cellFlag));//合并
                        }

                    }

                    // 规格1数据填充
                    if (cellFlag == 10)
                    {
                        cell.SetCellValue(String.Concat(listBags.ToArray()));
                    }
                    //规格2数据填充
                    else if (cellFlag == 12)
                    {
                        cell.SetCellValue(String.Concat(listContains.ToArray()));
                    }
                    //推广场次
                    else if (cellFlag == 8)
                    {

                        cell.SetCellValue(dt.Rows.Count);
                    }
                    else
                        cell.SetCellValue(dt.Rows[i][k].ToString());

                    cell.CellStyle = cellStyle1;
                    cellFlag++;
                }

                indexFlag++;
            }


        }
        rowIndexFlag = indexFlag;

    }

    public override void VerifyRenderingInServerForm(Control control)
    {
        // 重写(导出Excel)
    }

    #endregion

    HSSFWorkbook hssfworkbook;

    MemoryStream WriteToStream()
    {
        //Write the stream data of workbook to the root directory
        MemoryStream file = new MemoryStream();
        hssfworkbook.Write(file);
        return file;
    }
    void GenerateData(string name, DataTable dt)
    {
        if (name == "")
            name = DateTime.Now.ToString("yyyyMMddHHmmss");
        ISheet sheet1 = hssfworkbook.CreateSheet(name);//创建工作簿
        sheet1.CreateFreezePane(0, 1);//冻结一行0列
        IRow row = sheet1.CreateRow(0);
        row.Height = 500;
        ICellStyle cellStyle = hssfworkbook.CreateCellStyle();//创建单元格样式
        IDataFormat format = hssfworkbook.CreateDataFormat();//创建显示格式
        IFont font = hssfworkbook.CreateFont();//创建字体
        font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;//加粗

        font.FontHeight = 300;
        cellStyle.SetFont(font);

        cellStyle.Alignment = HorizontalAlignment.CENTER;//居中
        cellStyle.VerticalAlignment = VerticalAlignment.CENTER;

        cellStyle.BorderTop = cellStyle.BorderBottom = cellStyle.BorderLeft = cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;

        MyTable[] MyTables = new MyTable[dt.Columns.Count];//记录需要合并的列索引
        string TableName = Request.QueryString["Table"];
        ArrayList columns = new ArrayList();
        string sFile = Server.MapPath("Table.xml");
        XmlDocument doc = new XmlDocument();
        doc.Load(sFile);
        XmlNodeList list = doc.DocumentElement.SelectNodes("//Table [@Name='" + TableName + "']//Columns/Column");
        string columnname = "";
        foreach (ListItem li in chkDataTable.Items)
        {
            if (!li.Selected)
            {
                if (dt.Columns.Contains(li.Value))
                    dt.Columns.Remove(li.Value);//删除不需要显示的列
            }
        }

        dt.AcceptChanges();

        //设置列头
        int cindex = 0;
        foreach (XmlNode col in list)
        {
            columnname = col.Attributes.GetNamedItem("Name").Value;
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                if (columnname.ToUpper() == dt.Columns[j].ColumnName.ToUpper())
                {
                    ICell c = row.CreateCell(cindex);
                    string HeaderText = col.Attributes.GetNamedItem("HeaderText").Value;
                    c.SetCellValue(HeaderText);
                    MyTables[cindex] = new MyTable();
                    MyTables[cindex].AutoMerged = col.Attributes.GetNamedItem("AutoMerged").Value;//保存是否合并
                    MyTables[cindex].Width = col.Attributes.GetNamedItem("Width").Value;//列宽
                    MyTables[cindex].CellType = col.Attributes.GetNamedItem("Type").Value;//单元格类型
                    sheet1.SetColumnWidth(cindex, int.Parse(MyTables[cindex].Width) * 256);
                    columns.Add(j);//保存datatable列索引
                    if (c.StringCellValue == "")
                    {
                        c.SetCellValue(dt.Columns[cindex].ColumnName);
                    }
                    c.CellStyle = cellStyle;
                    cindex++;
                    break;
                }
            }

        }
        cellStyle = hssfworkbook.CreateCellStyle();//创建单元格样式
        cellStyle.BorderTop = cellStyle.BorderBottom = cellStyle.BorderLeft = cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;

        int[] startrowindex = new int[columns.Count];
        for (int i = 0; i < dt.Rows.Count; i++)
        {

            row = sheet1.CreateRow(i + 1);
            for (int j = 0; j < columns.Count; j++)
            {
                int cl = int.Parse(columns[j].ToString());//获取列索引
                ICell c = row.CreateCell(j);


                if (MyTables[j].CellType != "")//填充单元格数据类型
                {
                    try
                    {
                        switch (MyTables[j].CellType)
                        {

                            case "0":
                                c.SetCellValue(double.Parse(dt.Rows[i][cl].ToString()));
                                break;
                            case "1":
                                c.SetCellValue(dt.Rows[i][cl].ToString());
                                break;
                            case "2":
                                c.SetCellValue(bool.Parse(dt.Rows[i][cl].ToString()));
                                break;
                            default:
                                c.SetCellValue(dt.Rows[i][cl].ToString());
                                break;

                        }
                    }
                    catch
                    {
                        c.SetCellValue(dt.Rows[i][cl].ToString());
                    }
                }
                else
                    c.SetCellValue(dt.Rows[i][cl].ToString());//填充单元格数据
                if (MyTables[j].AutoMerged == "1" && i > 1)//合并单元格
                {
                    if (c.StringCellValue == sheet1.GetRow(i).Cells[j].StringCellValue)
                    {

                        cellStyle.Alignment = HorizontalAlignment.CENTER;//居中
                        cellStyle.VerticalAlignment = VerticalAlignment.CENTER;

                        if (startrowindex[j] == 0)
                            startrowindex[j] = i;//记录j列上一个相同值的行索引
                    }
                    else
                    {
                        if (startrowindex[j] != 0)
                        {
                            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(startrowindex[j], i, j, j));//合并
                            startrowindex[j] = 0;
                        }
                    }
                }
                c.CellStyle = cellStyle;
            }

        }

    }

    void InitializeWorkbook()
    {
        hssfworkbook = new HSSFWorkbook();

        ////create a entry of DocumentSummaryInformation
        DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
        dsi.Company = "AZYK";
        hssfworkbook.DocumentSummaryInformation = dsi;

        ////create a entry of SummaryInformation
        SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
        si.Subject = "AZYK";
        hssfworkbook.SummaryInformation = si;
    }


    void GenerateData1(string name, DataTable dt)
    {
        if (name == "")
            name = DateTime.Now.ToString("yyyyMMddHHmmss");
        ISheet sheet1 = hssfworkbook.CreateSheet(name);//创建工作簿
        sheet1.CreateFreezePane(1, 1);//冻结列头
        IRow row = sheet1.CreateRow(0);
        row.Height = 500;
        HSSFCellStyle cellStyle = (HSSFCellStyle)hssfworkbook.CreateCellStyle();//创建单元格样式
        HSSFDataFormat format = (HSSFDataFormat)hssfworkbook.CreateDataFormat();//创建显示格式
        HSSFFont font = (HSSFFont)hssfworkbook.CreateFont();//创建字体
        font.Boldweight = 4;
        font.FontHeight = 300;
        cellStyle.SetFont(font);
        cellStyle.BorderTop = cellStyle.BorderBottom = cellStyle.BorderLeft = cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;

        string TableName = Request.QueryString["Table"];
        ArrayList columns = new ArrayList();
        string sFile = Server.MapPath("Table.xml");
        XmlDocument doc = new XmlDocument();
        doc.Load(sFile);
        XmlNodeList list = doc.DocumentElement.SelectNodes("//Table [@Name='" + TableName + "']//Columns/Column");
        string columnname = "";
        foreach (ListItem li in chkDataTable.Items)
        {
            if (!li.Selected)
            {
                dt.Columns.Remove(li.Value);//删除不需要显示的列
            }
        }

        dt.AcceptChanges();
        //设置列头
        for (int j = 0; j < dt.Columns.Count; j++)
        {
            ICell c = row.CreateCell(j);
            foreach (XmlNode col in list)
            {
                columnname = col.Attributes.GetNamedItem("Name").Value;
                if (columnname.ToUpper() == dt.Columns[j].ColumnName.ToUpper())
                {
                    string HeaderText = col.Attributes.GetNamedItem("HeaderText").Value;
                    c.SetCellValue(HeaderText);
                    sheet1.SetColumnWidth(j, int.Parse(col.Attributes.GetNamedItem("Width").Value) * 256);
                    break;
                }

            }
            if (c.StringCellValue == "")
            {
                c.SetCellValue(dt.Columns[j].ColumnName);
            }
            c.CellStyle = cellStyle;
        }
        cellStyle = (HSSFCellStyle)hssfworkbook.CreateCellStyle();//创建单元格样式
        cellStyle.BorderTop = cellStyle.BorderBottom = cellStyle.BorderLeft = cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;


        for (int i = 1; i < dt.Rows.Count; i++)
        {
            row = sheet1.CreateRow(i);
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                ICell c = row.CreateCell(j);
                c.SetCellValue(dt.Rows[i][j].ToString());
                c.CellStyle = cellStyle;

            }
        }

    }


    protected void btn_Export_Click(object sender, EventArgs e)
    {

        if (IsVailed())
        {
            DateTime beginTime = DateTime.Parse(this.TxtStartDate.Text);
            DateTime endTime = DateTime.Parse(this.txtEndDate.Text);

            String userID = Request.QueryString["user"].ToString();
            //1.根据visits数据，查询记录出现的日期
            //2.根据出现的日期生成对于的sheet
            //3.根据不同日期下的visits，在sheet生成对应的记录和KPI,CPR信息
            StringBuilder sb_visit = new StringBuilder();
            sb_visit.AppendFormat(@" AND V.[START_TIME] >={0} 
                                     AND V.[STOP_TIME] <={1} 
                                     {2} 
                                     ORDER BY V.[STOP_TIME] ", beginTime.ToString("yyyyMMddHHmmss"), endTime.AddDays(1).ToString("yyyyMMddHHmmss"), userID == "0" ? "" : " AND V.[USER_ID]=" + userID);


            DataTable dt_visits = OrderHeaderManager.SearchHistoryCustomer(sb_visit.ToString()).Tables[0];
            if (dt_visits.Rows.Count == 0)
            {
                ScriptManager.RegisterStartupScript(this, this.GetType(), "", "alert('无数据')", true);
                return;
            }

            //初始化文件
            InitializeWorkbook();

            string lastSheetName = "";
            for (int i = 0; i < dt_visits.Rows.Count; i++)
            {
                String sheetName = BusinessLogic.GetDateTime(dt_visits.Rows[i]["START_TIME"].ToString(), "yyyyMMddHHmmss", "yyyy-MM-dd");
                if (sheetName != lastSheetName)
                {
                    //产生不同日期的sheet,填充数据
                    ISheet sheet = hssfworkbook.CreateSheet(sheetName);//创建工作簿
                    //列宽
                    sheet.SetColumnWidth(0, 10 * 256);
                    sheet.SetColumnWidth(1, 30 * 256);
                    sheet.SetColumnWidth(2, 50 * 256);
                    sheet.SetColumnWidth(3, 50 * 256);
                    //内容行1
                    IFont font_content = hssfworkbook.CreateFont();//创建字体
                    font_content.FontHeight = 215;
                    ICellStyle cellStyle = hssfworkbook.CreateCellStyle();//创建单元格样式
                    cellStyle.BorderTop = cellStyle.BorderBottom = cellStyle.BorderLeft = cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
                    cellStyle.SetFont(font_content);
                    //内容行2
                    IFont font_content1 = hssfworkbook.CreateFont();//创建字体
                    font_content.FontHeight = 215;
                    ICellStyle cellStyle1 = hssfworkbook.CreateCellStyle();//创建单元格样式
                    cellStyle1.BorderTop = cellStyle1.BorderBottom = cellStyle1.BorderLeft = cellStyle1.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
                    cellStyle1.SetFont(font_content);
                    cellStyle1.Alignment = HorizontalAlignment.CENTER;//居中
                    cellStyle1.VerticalAlignment = VerticalAlignment.CENTER;
                    //标题行1
                    ICellStyle cellStyleTitle = hssfworkbook.CreateCellStyle();//创建单元格样式
                    cellStyleTitle.BorderTop = cellStyleTitle.BorderBottom = cellStyleTitle.BorderLeft = cellStyleTitle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
                    IDataFormat format = hssfworkbook.CreateDataFormat();//创建显示格式
                    IFont font = hssfworkbook.CreateFont();//创建字体
                    font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;//加粗
                    font.FontHeight = 250;
                    cellStyleTitle.SetFont(font);
                    cellStyleTitle.Alignment = HorizontalAlignment.CENTER;//居中
                    cellStyleTitle.VerticalAlignment = VerticalAlignment.CENTER;
                    cellStyleTitle.FillForegroundColor = HSSFColor.LIGHT_ORANGE.index;
                    cellStyleTitle.FillPattern = FillPatternType.SOLID_FOREGROUND;




                    //标题行2
                    ICellStyle cellStyleTitle1 = hssfworkbook.CreateCellStyle();//创建单元格样式
                    cellStyleTitle1.BorderTop = cellStyleTitle1.BorderBottom = cellStyleTitle1.BorderLeft = cellStyleTitle1.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
                    IFont font1 = hssfworkbook.CreateFont();//创建字体
                    font1.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;//加粗
                    font1.FontHeight = 220;
                    cellStyleTitle1.SetFont(font1);
                    cellStyleTitle1.Alignment = HorizontalAlignment.CENTER;//居中
                    cellStyleTitle1.VerticalAlignment = VerticalAlignment.CENTER;
                    cellStyleTitle1.FillForegroundColor = HSSFColor.LIGHT_TURQUOISE.index;
                    cellStyleTitle1.FillPattern = FillPatternType.SOLID_FOREGROUND;

                    //获取当前日期的visit数据
                    IEnumerable<DataRow> query =
                    from temp in dt_visits.AsEnumerable()
                    where BusinessLogic.GetDateTime(temp.Field<String>("START_TIME").ToString(), "yyyyMMddHHmmss", "yyyy-MM-dd") == sheetName
                    select temp;
                    int RowIndex = 1;          //行记录
                    //循环每条visit数据，根据单条id获取kpi,cpr数据
                    DataTable dt = query.CopyToDataTable();
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {

                        IRow row = sheet.CreateRow(RowIndex);
                        row.Height = 500;
                        for (int c = 0; c < 4; c++)
                        {
                            ICell cell = row.CreateCell(c);
                            cell.CellStyle = cellStyleTitle;
                        }
                        row.Cells[0].SetCellValue(j + 1);  //序号
                        row.Cells[1].SetCellValue(dt.Rows[j]["NAME"].ToString());
                        row.Cells[2].SetCellValue(BusinessLogic.GetDateTime(dt.Rows[j]["START_TIME"].ToString(), "yyyyMMddHHmmss", "yyyy-MM-dd HH:mm:ss"));
                        row.Cells[3].SetCellValue(BusinessLogic.GetDateTime(dt.Rows[j]["STOP_TIME"].ToString(), "yyyyMMddHHmmss", "yyyy-MM-dd HH:mm:ss"));
                        String visitId = dt.Rows[j]["VISITID"].ToString();
                        int RowIndexFlag;
                        //加载kpi指标信息
                        GenerateKPIData(visitId, RowIndex + 1, sheet, cellStyleTitle1, cellStyle1, cellStyle, out RowIndexFlag);
                        //加载cpr分销数据信息
                        GenerateCPRData(visitId, RowIndexFlag + 1, sheet, cellStyleTitle1, cellStyle1, cellStyle, out RowIndexFlag);


                        RowIndex = RowIndexFlag;
                    }

                }
                lastSheetName = sheetName;


            }

            //定义输出类型
            HttpContext.Current.Response.Charset = "UTF-8";
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
            string filenameDetail = this.lblName.Text + "(" + beginTime.Month + "-" + beginTime.Day + "至" + endTime.Month + "-" + endTime.Day + ")拜访记录" + ".xls";
            string filename = HttpUtility.UrlEncode(filenameDetail, System.Text.Encoding.UTF8);
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
            Response.Clear();
            Response.BinaryWrite(WriteToStream().GetBuffer());
            Response.End();
        }
        else
        {
            ScriptManager.RegisterStartupScript(this, this.GetType(), "", "alert('时间跨度需在7天以内')", true);
        }

    }

    //private void GenerateCPRData(string visitId, int RowIndex, ISheet sheet, ICellStyle cellStyleTitle, ICellStyle cellStyle1, ICellStyle cellStyle, out int RowIndexFlag1)
    //{
    //    DataTable dt_CPR = new CPR_Data_Manager().GetCPR_BRAND_Table(visitId, MyStateManager.DomainID.ToString(), "1", "");
    //    RowIndexFlag1 = RowIndex + dt_CPR.Rows.Count;
    //    if (dt_CPR.Rows.Count == 0)
    //    {
    //        RowIndexFlag1 = RowIndex - 1;
    //        return;
    //    }
    //    //列表行
    //    IRow titleRow = sheet.CreateRow(RowIndex);
    //    for (int c = 1; c < 4; c++)
    //    {
    //        ICell cell = titleRow.CreateCell(c);
    //        cell.CellStyle = cellStyleTitle;

    //    }
    //    titleRow.Cells[0].SetCellValue("分销数据");
    //    titleRow.Cells[1].SetCellValue("指标明细");
    //    titleRow.Cells[2].SetCellValue("采集结果");

    //    RowIndex++;
    //    String last_cpr_name = "";
    //    for (int i = 0; i < dt_CPR.Rows.Count; i++)
    //    {
    //        String cpr_name = dt_CPR.Rows[i]["NAME"].ToString();
    //        if (cpr_name != last_cpr_name)
    //        {
    //            IEnumerable<DataRow> query =
    //            from temp in dt_CPR.AsEnumerable()
    //            where temp.Field<String>("NAME").ToString() == cpr_name
    //            select temp;

    //            DataTable dt = query.CopyToDataTable();
    //            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(RowIndex, RowIndex + dt.Rows.Count - 1, 1, 1));//合并
    //            for (int j = 0; j < dt.Rows.Count; j++)
    //            {
    //                IRow row = sheet.CreateRow(RowIndex);
    //                for (int c = 2; c < 4; c++)
    //                {
    //                    ICell cell = row.CreateCell(c);
    //                    cell.CellStyle = cellStyle;
    //                }
    //                row.Cells[0].SetCellValue(dt.Rows[j]["MEASUREMENTNAME"].ToString());

    //                //图片
    //                if ("6" == dt.Rows[j]["INPUTTYPE"].ToString().Trim() && !String.IsNullOrEmpty(dt.Rows[j]["PHOTO_ID"].ToString().Trim()))
    //                {
    //                    var photo = new PhotoManager().Load(dt.Rows[j]["PHOTO_ID"].ToString().Trim());
    //                    String FilePath = "";
    //                    if (photo != null)
    //                    {
    //                        FilePath = ImagePath + "\\" + photo.PHOTOPATH;
    //                    }
    //                    if (File.Exists(FilePath))
    //                    {
    //                        row.Height = 20 * 256;

    //                        //add picture data to this workbook.  

    //                        byte[] bytes = System.IO.File.ReadAllBytes(FilePath);
    //                        if (bytes.Length != 0)
    //                        {
    //                            int pictureIdx = hssfworkbook.AddPicture(bytes, PictureType.JPEG);
    //                            //create sheet ISheet sheet = hssfworkbook.CreateSheet("Sheet1");   
    //                            // Create the drawing patriarch.  This is the top level container for all shapes.  
    //                            HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
    //                            //add a picture  
    //                            HSSFClientAnchor anchor = new HSSFClientAnchor(10, 10, 1023, 0, 3, RowIndex, 1, 3);
    //                            patriarch.CreatePicture(anchor, pictureIdx).Resize();
    //                        }

    //                    }
    //                }
    //                else
    //                {
    //                    row.Cells[1].SetCellValue(dt.Rows[j]["VALUE"].ToString());
    //                }
    //                ICell cell1 = row.CreateCell(1);
    //                cell1.CellStyle = cellStyle1;
    //                cell1.SetCellValue(dt.Rows[j]["NAME"].ToString());

    //                RowIndex++;
    //            }
    //        }
    //        last_cpr_name = cpr_name;
    //    }
    //}

    //private void GenerateKPIData(string visitId, int RowIndex, ISheet sheet, ICellStyle cellStyleTitle, ICellStyle cellStyle1, ICellStyle cellStyle, out int RowIndexFlag)
    //{

    //    //行结束位置=起始位置+数据条数
    //    //kpi数据 
    //    DataTable dt_KPI = MeasuresGroupItemDetailValueManager.GetMeasuresTransactionInfo(" AND V.ID='" + visitId + "'");
    //    RowIndexFlag = RowIndex + dt_KPI.Rows.Count;
    //    if (dt_KPI.Rows.Count == 0)
    //    {
    //        RowIndexFlag = RowIndex - 1;
    //        return;
    //    }
    //    String last_kpi_name = "";
    //    //列表行
    //    IRow titleRow = sheet.CreateRow(RowIndex);
    //    for (int c = 1; c < 4; c++)
    //    {
    //        ICell cell = titleRow.CreateCell(c);
    //        cell.CellStyle = cellStyleTitle;
    //    }
    //    titleRow.Cells[0].SetCellValue("指标信息");
    //    titleRow.Cells[1].SetCellValue("指标明细");
    //    titleRow.Cells[2].SetCellValue("采集结果");
    //    RowIndex++;

    //    for (int i = 0; i < dt_KPI.Rows.Count; i++)
    //    {
    //        String kpi_name = dt_KPI.Rows[i]["MEASURE_NAME"].ToString();
    //        if (kpi_name != last_kpi_name)
    //        {
    //            IEnumerable<DataRow> query =
    //            from temp in dt_KPI.AsEnumerable()
    //            where temp.Field<String>("MEASURE_NAME").ToString() == kpi_name
    //            select temp;

    //            DataTable dt = query.CopyToDataTable();
    //            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(RowIndex, RowIndex + dt.Rows.Count - 1, 1, 1));//合并

    //            for (int j = 0; j < dt.Rows.Count; j++)
    //            {
    //                IRow row = sheet.CreateRow(RowIndex);
    //                for (int c = 2; c < 4; c++)
    //                {
    //                    ICell cell = row.CreateCell(c);
    //                    cell.CellStyle = cellStyle;
    //                }
    //                row.Cells[0].SetCellValue(dt.Rows[j]["DETAILS"].ToString());
    //                row.Cells[1].SetCellValue(dt.Rows[j]["VALUE"].ToString());

    //                ICell cell1 = row.CreateCell(1);
    //                cell1.SetCellValue(dt.Rows[j]["MEASURE_NAME"].ToString());
    //                cell1.CellStyle = cellStyle1;
    //                RowIndex++;
    //            }
    //        }
    //        last_kpi_name = kpi_name;
    //    }

    //}

    private void GenerateCPRData(string visitId, int RowIndex, ISheet sheet, ICellStyle cellStyleTitle, ICellStyle cellStyle1, ICellStyle cellStyle, out int RowIndexFlag1)
    {
        DataTable dt_CPR1 = MeasuresGroupItemDetailValueManager.GetCPRinfo(visitId, MyStateManager.DomainID.ToString(), "0");//本品数据
        DataTable dt_CPR2 = MeasuresGroupItemDetailValueManager.GetCPRinfo(visitId, MyStateManager.DomainID.ToString(), "1");//竟品数据

        if (dt_CPR1.Rows.Count == 0 && dt_CPR2.Rows.Count == 0)
        {
            RowIndexFlag1 = RowIndex;
            return;
        }

        //创建本品单元格
        if (dt_CPR1.Rows.Count > 0)
        {
            IRow rowbptitle = sheet.CreateRow(RowIndex);
            int tempcolumn = 1;
            for (int i = 3; i < dt_CPR1.Columns.Count; i++)
            {
                ICell celltitle = rowbptitle.CreateCell(tempcolumn);
                celltitle.CellStyle = cellStyleTitle;
                celltitle.SetCellValue(dt_CPR1.Columns[i].ColumnName);
                sheet.SetColumnWidth(i, 30 * 256);
                tempcolumn++;
            }
            string _brandId = string.Empty;
            for (int r = 0; r < dt_CPR1.Rows.Count; r++)
            {
                IRow rowbpcontent = sheet.CreateRow(RowIndex + r + 1);
                tempcolumn = 1;
                for (int c = 3; c < dt_CPR1.Columns.Count; c++)
                {
                    if (dt_CPR1.Columns[c].ColumnName == "近景整体")
                    {
                        ICell cellphoto = rowbpcontent.CreateCell(tempcolumn);
                        cellphoto.CellStyle = cellStyle1;
                        if (!string.IsNullOrEmpty(dt_CPR1.Rows[r]["近景整体"].ToString().Trim()))
                        {
                            var photo = new PhotoManager().Load(dt_CPR1.Rows[r]["近景整体"].ToString().Trim());
                            String FilePath = "";
                            addPhoto(photo, FilePath, rowbpcontent, sheet, tempcolumn, r + RowIndex);
                        }
                        tempcolumn++;
                    }
                    else if (dt_CPR1.Columns[c].ColumnName == "远景整体")
                    {
                        ICell cellphoto = rowbpcontent.CreateCell(tempcolumn);
                        cellphoto.CellStyle = cellStyle1;
                        if (!string.IsNullOrEmpty(dt_CPR1.Rows[r]["远景整体"].ToString().Trim()))
                        {
                            PHOTO photo = new PhotoManager().Load(dt_CPR1.Rows[r]["远景整体"].ToString().Trim());
                            String FilePath = "";
                            addPhoto(photo, FilePath, rowbpcontent, sheet, tempcolumn, r + RowIndex);
                        }
                        tempcolumn++;
                    }
                    else if (dt_CPR1.Columns[c].ColumnName == "促销员")
                    {
                        ICell cellphoto = rowbpcontent.CreateCell(tempcolumn);
                        cellphoto.CellStyle = cellStyle1;
                        if (!string.IsNullOrEmpty(dt_CPR1.Rows[r]["促销员"].ToString().Trim()))
                        {
                            var photo = new PhotoManager().Load(dt_CPR1.Rows[r]["促销员"].ToString().Trim());
                            String FilePath = "";
                            addPhoto(photo, FilePath, rowbpcontent, sheet, tempcolumn, r + RowIndex);
                        }
                        tempcolumn++;
                    }
                    else if (dt_CPR1.Columns[c].ColumnName == "品牌名称")
                    {
                        if (!string.IsNullOrEmpty(dt_CPR1.Rows[r]["品牌名称"].ToString().Trim()))
                        {
                            string bid = dt_CPR1.Rows[r]["品牌名称"].ToString();
                            ICell cellcontent = rowbpcontent.CreateCell(tempcolumn);
                            cellcontent.CellStyle = cellStyle1;
                            if (bid != _brandId)
                            {
                                cellcontent.SetCellValue(bid);
                                int rgroup = (dt_CPR1.Select("品牌名称 = " + "'" + bid + "'")).Count();
                                sheet.AddMergedRegion(new CellRangeAddress(RowIndex + 1, RowIndex + rgroup, tempcolumn, tempcolumn));
                                _brandId = bid;
                            }
                            tempcolumn++;
                        }
                    }
                    else
                    {
                        ICell cellcontent = rowbpcontent.CreateCell(tempcolumn);
                        cellcontent.CellStyle = cellStyle1;
                        cellcontent.SetCellValue(dt_CPR1.Rows[r][c].ToString());
                        sheet.SetColumnWidth(tempcolumn, 30 * 256);
                        tempcolumn++;
                    }
                }
            }
            RowIndex += dt_CPR1.Rows.Count + 1;
        }

        //创建竟品单元格
        if (dt_CPR2.Rows.Count > 0)
        {
            string _brandId = string.Empty;
            IRow rowjptitle = sheet.CreateRow(RowIndex);
            int tempcolindex = 1;
            for (int i = 3; i < dt_CPR2.Columns.Count; i++)
            {
                ICell celltitle = rowjptitle.CreateCell(tempcolindex);
                celltitle.CellStyle = cellStyleTitle;
                celltitle.SetCellValue(dt_CPR2.Columns[i].ColumnName);
                sheet.SetColumnWidth(i, 30 * 256);
                tempcolindex++;
            }

            for (int r = 0; r < dt_CPR2.Rows.Count; r++)
            {
                tempcolindex = 1;
                IRow rowjpcontent = sheet.CreateRow(RowIndex + r + 1);
                for (int c = 3; c < dt_CPR2.Columns.Count; c++)
                {
                    if (dt_CPR2.Columns[c].ColumnName == "推广照片")
                    {
                        //ICell cellphoto = rowjpcontent.CreateCell(c);
                        ICell cellphoto = rowjpcontent.CreateCell(tempcolindex);
                        cellphoto.CellStyle = cellStyle1;
                        if (!string.IsNullOrEmpty(dt_CPR2.Rows[r]["推广照片"].ToString().Trim()))
                        {
                            var photo = new PhotoManager().Load(dt_CPR2.Rows[r]["推广照片"].ToString().Trim());
                            String FilePath = "";
                            //addPhoto(photo, FilePath, rowjpcontent, sheet, c, RowIndex);
                            addPhoto(photo, FilePath, rowjpcontent, sheet, tempcolindex, r + RowIndex);
                            //tempcolindex++;
                        }
                    }
                    else if (dt_CPR2.Columns[c].ColumnName == "品牌名称")
                    {

                        ICell cellphoto = rowjpcontent.CreateCell(tempcolindex);
                        cellphoto.CellStyle = cellStyle1;
                        if (!string.IsNullOrEmpty(dt_CPR2.Rows[r]["品牌名称"].ToString().Trim()))
                        {
                            string bid = dt_CPR2.Rows[r][c].ToString();
                            if (bid != _brandId)
                            {
                                ICell cellcontent = rowjpcontent.CreateCell(tempcolindex);
                                cellcontent.CellStyle = cellStyle1;
                                cellcontent.SetCellValue(dt_CPR2.Rows[r]["品牌名称"].ToString());
                                int rgroup = (dt_CPR2.Select("品牌名称 = " + "'" + bid + "'")).Count();
                                //sheet.AddMergedRegion(new CellRangeAddress(RowIndex + 1, RowIndex + rgroup, c, c));
                                sheet.AddMergedRegion(new CellRangeAddress(RowIndex + 1, RowIndex + rgroup, tempcolindex, tempcolindex));
                                _brandId = bid;
                                tempcolindex++;
                            }
                        }
                    }
                    else
                    {
                        ICell cellcontent = rowjpcontent.CreateCell(tempcolindex);
                        cellcontent.CellStyle = cellStyle1;
                        cellcontent.SetCellValue(dt_CPR2.Rows[r][c].ToString());
                        sheet.SetColumnWidth(c, 30 * 256);
                        tempcolindex++;
                    }
                }
            }
            RowIndex += dt_CPR2.Rows.Count + 1;
        }
        RowIndexFlag1 = RowIndex;
    }

    //加载图片
    private void addPhoto(PHOTO photo, string FilePath, IRow row, ISheet sheet, int cellindex, int rowindex)
    {
        if (photo != null)
        {
            FilePath = ImagePath + "\\" + photo.PHOTOPATH;
        }
        if (File.Exists(FilePath))
        {
            row.Height = 25 * 256;

            //add picture data to this workbook.  

            byte[] bytes = System.IO.File.ReadAllBytes(FilePath);
            if (bytes.Length != 0)
            {
                sheet.SetColumnWidth(cellindex, 58 * 256);
                int pictureIdx = hssfworkbook.AddPicture(bytes, PictureType.JPEG);

                //create sheet ISheet sheet = hssfworkbook.CreateSheet("Sheet1");   
                // Create the drawing patriarch.  This is the top level container for all shapes.  
                HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
                //add a picture  
                HSSFClientAnchor anchor = new HSSFClientAnchor(8, 8, 1000, 0, cellindex, rowindex + 1, cellindex, rowindex + 1);
                patriarch.CreatePicture(anchor, pictureIdx).Resize(1);
            }

        }
    }

    private void GenerateKPIData(string visitId, int RowIndex, ISheet sheet, ICellStyle cellStyleTitle, ICellStyle cellStyle1, ICellStyle cellStyle, out int RowIndexFlag)
    {
        //行结束位置=起始位置+数据条数
        DataTable dt_KPI1 = MeasuresGroupItemDetailValueManager.GetKPIinfo(visitId, "竞品排面检查");
        DataTable dt_KPI2 = MeasuresGroupItemDetailValueManager.GetKPIinfo(visitId, "竞品新品信息");
        DataTable dt_KPI3 = MeasuresGroupItemDetailValueManager.GetKPIinfo(visitId, "卖场信息调查");
        if (dt_KPI1.Rows.Count == 0 && dt_KPI2.Rows.Count == 0 && dt_KPI3.Rows.Count == 0)
        {
            RowIndexFlag = RowIndex - 1;
            return;
        }

        //竞品排面检查
        if (dt_KPI1.Rows.Count > 0)
        {
            IRow rowjctitle = sheet.CreateRow(RowIndex);
            IRow rowjccontent = sheet.CreateRow(RowIndex + 1);
            for (int i = 1; i < dt_KPI1.Columns.Count + 1; i++)
            {

                ICell celltitle = rowjctitle.CreateCell(i);
                celltitle.CellStyle = cellStyleTitle;
                celltitle.SetCellValue(dt_KPI1.Columns[i - 1].ColumnName);
                ICell cellcontent = rowjccontent.CreateCell(i);
                cellcontent.CellStyle = cellStyle1;
                cellcontent.SetCellValue(dt_KPI1.Rows[0][i - 1].ToString());
                sheet.SetColumnWidth(i, 30 * 256);
            }
            RowIndex += dt_KPI1.Rows.Count + 1;
        }

        //竞品新品信息
        if (dt_KPI2.Rows.Count > 0)
        {
            IRow rowjctitle = sheet.CreateRow(RowIndex);
            IRow rowjccontent = sheet.CreateRow(RowIndex + 1);
            for (int i = 1; i < dt_KPI2.Columns.Count + 1; i++)
            {

                ICell celltitle = rowjctitle.CreateCell(i);
                celltitle.CellStyle = cellStyleTitle;
                celltitle.SetCellValue(dt_KPI2.Columns[i - 1].ColumnName);
                ICell cellcontent = rowjccontent.CreateCell(i);
                cellcontent.CellStyle = cellStyle1;
                cellcontent.SetCellValue(dt_KPI2.Rows[0][i - 1].ToString());
                sheet.SetColumnWidth(i, 30 * 256);
            }
            RowIndex += dt_KPI2.Rows.Count + 1;
        }

        //卖场信息调查
        if (dt_KPI3.Rows.Count > 0)
        {
            IRow rowjctitle = sheet.CreateRow(RowIndex);
            IRow rowjccontent = sheet.CreateRow(RowIndex + 1);
            for (int i = 1; i < dt_KPI3.Columns.Count + 1; i++)
            {

                ICell celltitle = rowjctitle.CreateCell(i);
                celltitle.CellStyle = cellStyleTitle;
                celltitle.SetCellValue(dt_KPI3.Columns[i - 1].ColumnName);
                ICell cellcontent = rowjccontent.CreateCell(i);
                cellcontent.CellStyle = cellStyle1;
                cellcontent.SetCellValue(dt_KPI3.Rows[0][i - 1].ToString());
                sheet.SetColumnWidth(i, 30 * 256);
            }
            RowIndex += dt_KPI3.Rows.Count + 1;
        }
        RowIndexFlag = RowIndex - 1;
    }



    //对输入时间验证，大于7天的，为验证失败
    public Boolean IsVailed()
    {

        if (String.IsNullOrWhiteSpace(TxtStartDate.Text.Trim()) || String.IsNullOrWhiteSpace(txtEndDate.Text.Trim()))
        {
            return false;
        }
        else
        {
            DateTime beginTime = DateTime.Parse(this.TxtStartDate.Text);
            DateTime endTime = DateTime.Parse(this.txtEndDate.Text);

            int day = (endTime - beginTime).Days;

            return day < 7 ? true : false;
        }

    }

    protected void txtExport0_Click(object sender, EventArgs e)
    {
        GenerateVisitBaseInfo();
    }
    //导出活动点数人次
    protected void btn_Export2_Click(object sender, EventArgs e)
    {
        string startime = "";
        string endtime = "";
        string endtime1 = "";
        string message;
        if (!IsValid(out message))
        {
            ScriptManager.RegisterStartupScript(this, this.GetType(), "", "alert('" + message + "')", true);
            return;
        }
        else
        {
            startime = Convert.ToDateTime(TxtStartDate.Text).ToString("yyyyMMddHHmmss");
            endtime = Convert.ToDateTime(txtEndDate.Text).ToString("yyyyMMddHHmmss");
            endtime1 = Convert.ToDateTime(txtEndDate.Text).AddDays(1).ToString("yyyyMMddHHmmss");
            int dominid = Convert.ToInt32(MyStateManager.DomainID);
            DataTable table = GetData(startime, endtime1, dominid, "CXRS");
            ExprotExcel(CreateTable(table, startime, endtime));
        }

    }

    #region 导出活动点数人次
    int total = 0;
    int totalCustomer = 0;
    private DataTable GetData(string mintime, string maxtime, int domainid, string code)
    {
        DataTable dt = new DataTable();
        dt = MeasuresGroupItemDetailValueManager.GetPromotionPersonReport(mintime, maxtime, domainid, code);
        return dt;
    }

    private DataTable CreateTable(DataTable table, string time1, string time2)
    {
        //table = GetData("21021207", "20121210", 26, "CXRS");
        var query0 =
            from temp in table.AsEnumerable().Select(s => s.Field<String>("customername")).Distinct()
            select temp;
        totalCustomer = query0.Count<String>();
        var query1 =
                from temp in table.AsEnumerable().Select(s => s.Field<String>("seller")).Distinct()
                select temp;
        string[] sellerList = null;
        if (query1.Count<String>() > 0)
        {
            sellerList = query1.ToArray<String>();
        }
        else
        {
            sellerList = new string[0];
        }
        //创建临时表
        DataTable dttemp = new DataTable("TempTable");

        DataColumn column1 = new DataColumn("促销员", typeof(string)); //促销员列            
        dttemp.Columns.Add(column1);
        DateTime DateTime1 = Convert.ToDateTime(BusinessLogic.GetDateTime(time1, "yyyyMMddHHmmss", "yyyy-MM-dd HH:mm:ss"));
        DateTime DateTime2 = Convert.ToDateTime(BusinessLogic.GetDateTime(time2, "yyyyMMddHHmmss", "yyyy-MM-dd HH:mm:ss"));
        //根据时间创建列
        for (DateTime dt = DateTime1; dt < DateTime2.AddDays(1); dt = dt.AddDays(1))
        {
            DataColumn columnSeller = new DataColumn(dt.Month + "." + dt.Day, typeof(string));
            columnSeller.AllowDBNull = true;
            dttemp.Columns.Add(columnSeller);
        }
        DataColumn column2 = new DataColumn("人次小计", typeof(int));
        dttemp.Columns.Add(column2); //合计列

        //为每个促销员创建行        
        for (int i = 0; i < sellerList.Length; i++)
        {
            IEnumerable<DataRow> query2 = from temp in table.AsEnumerable()
                                          where temp.Field<string>("seller") == sellerList[i]
                                          select temp;
            //;  s.CopyToDataTable();
            DataRow row = dttemp.NewRow();
            row["促销员"] = sellerList[i];
            for (DateTime dt = DateTime1; dt < DateTime2.AddDays(1); dt = dt.AddDays(1))
            {
                IEnumerable<String> query3 =
                 from temp in query2
                 where Convert.ToDateTime(BusinessLogic.GetDateTime(temp.Field<String>("startime") + "000000", "yyyyMMddHHmmss", "yyyy-MM-dd HH:mm:ss")) == dt
                 select temp.Field<string>("customername");
                //if (query3.ToArray().Length > 0)
                //    row[dt.Month + "." + dt.Day] = query3.ToArray()[0];
                if (query3.ToArray().Length > 0)
                {
                    for (int m = 0; m < query3.ToArray().Length; m++)
                        row[dt.Month + "." + dt.Day] += query3.ToArray()[m] + ",";//一个促销员一天促销的所有店
                    row[dt.Month + "." + dt.Day] = row[dt.Month + "." + dt.Day].ToString().Substring(0, row[dt.Month + "." + dt.Day].ToString().Length - 1);
                }
            }
            row["人次小计"] = query2.Count<DataRow>();
            dttemp.Rows.Add(row);
            total = total + query2.Count<DataRow>();
        }
        return dttemp;
    }

    //HSSFWorkbook hssfworkbook = new HSSFWorkbook();
    private void ExprotExcel(DataTable dt)
    {
        if (dt.Rows.Count == 0)
        {
            ScriptManager.RegisterStartupScript(this, this.GetType(), "", "alert('没有数据')", true);
            return;
        }
        DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
        SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
        InitializeWorkbook();
        ISheet sheet = hssfworkbook.CreateSheet("促销员活动点数人次");
        ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
        cellStyle.Alignment = HorizontalAlignment.CENTER;
        cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
        cellStyle.BorderTop = cellStyle.BorderBottom = cellStyle.BorderLeft = cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
        //设置字体
        IFont font = hssfworkbook.CreateFont();
        font.FontHeightInPoints = 13;
        cellStyle.SetFont(font);

        IRow rowtitle = sheet.CreateRow(1);
        rowtitle.HeightInPoints = 30;
        ICell celltitle = rowtitle.CreateCell(1);
        celltitle.SetCellValue("促销员活动点数人次");
        //IFont font1 = hssworkbook.CreateFont();
        //font1.FontName = "黑体";
        celltitle.CellStyle = cellStyle;
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 1, dt.Columns.Count));//合并单元格

        for (int i = 2; i < dt.Columns.Count; i++)
        {
            rowtitle.CreateCell(i).CellStyle = cellStyle;
        }

        IRow rowheader = sheet.CreateRow(2);
        for (int k = 0; k < dt.Columns.Count; k++)
        {
            ICell cell = rowheader.CreateCell(k + 1);
            cell.SetCellValue(dt.Columns[k].ColumnName);
            cell.CellStyle = cellStyle;
            sheet.SetColumnWidth(k + 1, 7500);
        }
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            IRow row = sheet.CreateRow(i + 3);
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                ICell cell = row.CreateCell(j + 1);

                if (!string.IsNullOrEmpty(dt.Rows[i][j].ToString()))
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                cell.CellStyle = cellStyle;
            }
        }

        IRow row2 = sheet.CreateRow(dt.Rows.Count + 3);
        row2.HeightInPoints = 20;
        ICell cell2 = row2.CreateCell(1);
        cell2.SetCellValue("促销员工作人次");
        ICellStyle cellStyle2 = hssfworkbook.CreateCellStyle();
        cellStyle2.Alignment = HorizontalAlignment.CENTER;
        cellStyle2.VerticalAlignment = VerticalAlignment.CENTER;
        cellStyle2.FillForegroundColor = HSSFColor.LIGHT_CORNFLOWER_BLUE.index;
        cellStyle2.FillPattern = FillPatternType.SOLID_FOREGROUND;
        cellStyle2.BorderTop = cellStyle2.BorderBottom = cellStyle2.BorderLeft = cellStyle2.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
        cell2.CellStyle = cellStyle2;
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(dt.Rows.Count + 3, dt.Rows.Count + 3, 1, dt.Columns.Count - 1));//合并单元格


        ICell cell22 = row2.CreateCell(dt.Columns.Count);
        cell22.SetCellValue(total);
        cell22.CellStyle = cellStyle;

        IRow row3 = sheet.CreateRow(dt.Rows.Count + 4);
        row3.HeightInPoints = 25;//设置行高
        ICell cell3 = row3.CreateCell(1);
        cell3.SetCellValue("卖场点数总计");
        ICellStyle cellStyle3 = hssfworkbook.CreateCellStyle();
        cellStyle3.Alignment = HorizontalAlignment.CENTER;
        cellStyle3.VerticalAlignment = VerticalAlignment.CENTER;
        cellStyle3.FillForegroundColor = HSSFColor.GREEN.index;
        cellStyle3.FillPattern = FillPatternType.SOLID_FOREGROUND;

        cellStyle3.SetFont(font);
        cellStyle3.BorderTop = cellStyle3.BorderBottom = cellStyle3.BorderLeft = cellStyle3.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
        cell3.CellStyle = cellStyle3;
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(dt.Rows.Count + 4, dt.Rows.Count + 4, 1, dt.Columns.Count - 1)); //合并


        for (int i = 2; i < dt.Columns.Count; i++)
        {
            row3.CreateCell(i).CellStyle = cellStyle;
        }


        ICell cell33 = row3.CreateCell(dt.Columns.Count);
        cell33.SetCellValue(totalCustomer);
        cell33.CellStyle = cellStyle;

        //定义输出类型
        HttpContext.Current.Response.Charset = "UTF_8";
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
        string filenameDetail = "促销员活动点数人次" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
        string filename = HttpUtility.UrlEncode(filenameDetail, System.Text.Encoding.UTF8);
        Response.ContentType = "application/vnd.ms-excel";
        Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
        Response.Clear();
        Response.BinaryWrite(WriteToStream().GetBuffer());
        Response.End();
    }

    //对输入时间验证，大于8天的，为验证失败
    public Boolean IsValid(out string message)
    {
        int day = 0;
        message = "";
        try
        {
            if (String.IsNullOrWhiteSpace(TxtStartDate.Text.Trim()) || String.IsNullOrWhiteSpace(txtEndDate.Text.Trim()))
            {
                return false;
            }
            else
            {
                DateTime beginTime = DateTime.Parse(this.TxtStartDate.Text);
                DateTime endTime = DateTime.Parse(this.txtEndDate.Text);

                day = (endTime - beginTime).Days;
            }
        }
        catch (Exception ee)
        {
            message = "时间格式不对，请重新输入";
            return false;
        }
        if (day > 8)
            message = "时间跨度超过8天，请重选";
        return day <= 8 ? true : false;
    }
    #endregion


    #region 纵向导出详细数据
    protected void btn_ExportZX_Click(object sender, EventArgs e)
    {
        //visits以时间分sheet;根据visitID循环kpi，循环cpr

        if (IsVailed())
        {
            DateTime beginTime = DateTime.Parse(this.TxtStartDate.Text);
            DateTime endTime = DateTime.Parse(this.txtEndDate.Text);

            String userID = Request.QueryString["user"].ToString();
            //1.根据visits数据，查询记录出现的日期
            //2.根据出现的日期生成对于的sheet
            //3.根据不同日期下的visits，在sheet生成对应的记录和KPI,CPR信息
            StringBuilder sb_visit = new StringBuilder();
            sb_visit.AppendFormat(@" AND V.[START_TIME] >={0} 
                                     AND V.[STOP_TIME] <={1} 
                                     {2} 
                                     ORDER BY V.[STOP_TIME] ", beginTime.ToString("yyyyMMddHHmmss"), endTime.AddDays(1).ToString("yyyyMMddHHmmss"), userID == "0" ? "" : " AND V.[USER_ID]=" + userID);

            DataTable dt_visits = OrderHeaderManager.SearchHistoryCustomer(sb_visit.ToString()).Tables[0];
            if (dt_visits.Rows.Count == 0)
            {
                ScriptManager.RegisterStartupScript(this, this.GetType(), "", "alert('无数据')", true);
                return;
            }

            //初始化文件
            InitializeWorkbook();

            string lastSheetName = "";
            for (int i = 0; i < dt_visits.Rows.Count; i++)
            {
                String sheetName = BusinessLogic.GetDateTime(dt_visits.Rows[i]["START_TIME"].ToString(), "yyyyMMddHHmmss", "yyyy-MM-dd");
                if (sheetName != lastSheetName)
                {
                    //产生不同日期的sheet,填充数据
                    ISheet sheet = hssfworkbook.CreateSheet(sheetName);//创建工作簿
                    //列宽
                    sheet.SetColumnWidth(0, 10 * 256);
                    sheet.SetColumnWidth(1, 30 * 256);
                    sheet.SetColumnWidth(2, 50 * 256);
                    sheet.SetColumnWidth(3, 50 * 256);
                    //内容行1
                    IFont font_content = hssfworkbook.CreateFont();//创建字体
                    font_content.FontHeight = 215;
                    ICellStyle cellStyle = hssfworkbook.CreateCellStyle();//创建单元格样式
                    cellStyle.BorderTop = cellStyle.BorderBottom = cellStyle.BorderLeft = cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
                    cellStyle.SetFont(font_content);
                    //内容行2
                    IFont font_content1 = hssfworkbook.CreateFont();//创建字体
                    font_content.FontHeight = 215;
                    ICellStyle cellStyle1 = hssfworkbook.CreateCellStyle();//创建单元格样式
                    cellStyle1.BorderTop = cellStyle1.BorderBottom = cellStyle1.BorderLeft = cellStyle1.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
                    cellStyle1.SetFont(font_content);
                    cellStyle1.Alignment = HorizontalAlignment.CENTER;//居中
                    cellStyle1.VerticalAlignment = VerticalAlignment.CENTER;
                    //标题行1
                    ICellStyle cellStyleTitle = hssfworkbook.CreateCellStyle();//创建单元格样式
                    cellStyleTitle.BorderTop = cellStyleTitle.BorderBottom = cellStyleTitle.BorderLeft = cellStyleTitle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
                    IDataFormat format = hssfworkbook.CreateDataFormat();//创建显示格式
                    IFont font = hssfworkbook.CreateFont();//创建字体
                    font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;//加粗
                    font.FontHeight = 250;
                    cellStyleTitle.SetFont(font);
                    cellStyleTitle.Alignment = HorizontalAlignment.CENTER;//居中
                    cellStyleTitle.VerticalAlignment = VerticalAlignment.CENTER;
                    cellStyleTitle.FillForegroundColor = HSSFColor.LIGHT_ORANGE.index;
                    cellStyleTitle.FillPattern = FillPatternType.SOLID_FOREGROUND;




                    //标题行2
                    ICellStyle cellStyleTitle1 = hssfworkbook.CreateCellStyle();//创建单元格样式
                    cellStyleTitle1.BorderTop = cellStyleTitle1.BorderBottom = cellStyleTitle1.BorderLeft = cellStyleTitle1.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
                    IFont font1 = hssfworkbook.CreateFont();//创建字体
                    font1.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;//加粗
                    font1.FontHeight = 220;
                    cellStyleTitle1.SetFont(font1);
                    cellStyleTitle1.Alignment = HorizontalAlignment.CENTER;//居中
                    cellStyleTitle1.VerticalAlignment = VerticalAlignment.CENTER;
                    cellStyleTitle1.FillForegroundColor = HSSFColor.LIGHT_TURQUOISE.index;
                    cellStyleTitle1.FillPattern = FillPatternType.SOLID_FOREGROUND;




                    //获取当前日期的visit数据
                    IEnumerable<DataRow> query =
                    from temp in dt_visits.AsEnumerable()
                    where BusinessLogic.GetDateTime(temp.Field<String>("START_TIME").ToString(), "yyyyMMddHHmmss", "yyyy-MM-dd") == sheetName
                    select temp;
                    int RowIndex = 1;          //行记录
                    //循环每条visit数据，根据单条id获取kpi,cpr数据
                    DataTable dt = query.CopyToDataTable();
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {

                        IRow row = sheet.CreateRow(RowIndex);
                        row.Height = 500;
                        for (int c = 0; c < 4; c++)
                        {
                            ICell cell = row.CreateCell(c);
                            cell.CellStyle = cellStyleTitle;
                        }
                        row.Cells[0].SetCellValue(j + 1);  //序号
                        row.Cells[1].SetCellValue(dt.Rows[j]["NAME"].ToString());
                        row.Cells[2].SetCellValue(BusinessLogic.GetDateTime(dt.Rows[j]["START_TIME"].ToString(), "yyyyMMddHHmmss", "yyyy-MM-dd HH:mm:ss"));
                        row.Cells[3].SetCellValue(BusinessLogic.GetDateTime(dt.Rows[j]["STOP_TIME"].ToString(), "yyyyMMddHHmmss", "yyyy-MM-dd HH:mm:ss"));
                        String visitId = dt.Rows[j]["VISITID"].ToString();
                        int RowIndexFlag;
                        //加载kpi指标信息
                        GenerateKPIData_ZX(visitId, RowIndex + 1, sheet, cellStyleTitle1, cellStyle1, cellStyle, out RowIndexFlag);
                        //加载cpr分销数据信息
                        GenerateCPRData_ZX(visitId, RowIndexFlag + 1, sheet, cellStyleTitle1, cellStyle1, cellStyle, out RowIndexFlag);

                        RowIndex = (RowIndexFlag + 1);
                    }

                }
                lastSheetName = sheetName;


            }

            //定义输出类型
            HttpContext.Current.Response.Charset = "UTF-8";
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
            string filenameDetail = this.lblName.Text + "(" + beginTime.Month + "-" + beginTime.Day + "至" + endTime.Month + "-" + endTime.Day + ")拜访记录" + ".xls";
            string filename = HttpUtility.UrlEncode(filenameDetail, System.Text.Encoding.UTF8);
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
            Response.Clear();
            Response.BinaryWrite(WriteToStream().GetBuffer());
            Response.End();

        }
    }
    private void GenerateKPIData_ZX(string visitId, int RowIndex, ISheet sheet, ICellStyle cellStyleTitle, ICellStyle cellStyle1, ICellStyle cellStyle, out int RowIndexFlag)
    {
        //行结束位置=起始位置+数据条数
        //kpi数据 
        DataTable dt_KPI = MeasuresGroupItemDetailValueManager.GetMeasuresTransactionInfo(" AND V.ID='" + visitId + "'");
        RowIndexFlag = RowIndex + dt_KPI.Rows.Count;
        if (dt_KPI.Rows.Count == 0)
        {
            RowIndexFlag = RowIndex - 1;
            return;
        }
        String last_kpi_name = "";
        //列表行
        IRow titleRow = sheet.CreateRow(RowIndex);
        for (int c = 1; c < 4; c++)
        {
            ICell cell = titleRow.CreateCell(c);
            cell.CellStyle = cellStyleTitle;

        }
        titleRow.Cells[0].SetCellValue("指标信息");
        titleRow.Cells[1].SetCellValue("指标明细");
        titleRow.Cells[2].SetCellValue("采集结果");
        RowIndex++;

        for (int i = 0; i < dt_KPI.Rows.Count; i++)
        {
            String kpi_name = dt_KPI.Rows[i]["MEASURE_NAME"].ToString();
            if (kpi_name != last_kpi_name)
            {
                IEnumerable<DataRow> query =
                from temp in dt_KPI.AsEnumerable()
                where temp.Field<String>("MEASURE_NAME").ToString() == kpi_name
                select temp;

                DataTable dt = query.CopyToDataTable();
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(RowIndex, RowIndex + dt.Rows.Count - 1, 1, 1));//合并

                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    IRow row = sheet.CreateRow(RowIndex);
                    for (int c = 2; c < 4; c++)
                    {
                        ICell cell = row.CreateCell(c);
                        cell.CellStyle = cellStyle;
                    }
                    row.Cells[0].SetCellValue(dt.Rows[j]["DETAILS"].ToString());
                    row.Cells[1].SetCellValue(dt.Rows[j]["VALUE"].ToString());

                    ICell cell1 = row.CreateCell(1);
                    cell1.SetCellValue(dt.Rows[j]["MEASURE_NAME"].ToString());
                    cell1.CellStyle = cellStyle1;
                    RowIndex++;
                }
            }
            last_kpi_name = kpi_name;
        }

    }

    private void GenerateCPRData_ZX(string visitId, int RowIndex, ISheet sheet, ICellStyle cellStyleTitle, ICellStyle cellStyle1, ICellStyle cellStyle, out int RowIndexFlag1)
    {
        //DataTable dt_CPR = new CPR_Data_Manager().GetCPR_BRAND_Table(visitId, MyStateManager.DomainID.ToString(), "1", "");
        DataTable dt_CPR = new CPR_Form_Manager().GetCPR_FROM_Table(visitId, MyStateManager.DomainID.ToString(), "1", "");
        RowIndexFlag1 = RowIndex + dt_CPR.Rows.Count;
        if (dt_CPR.Rows.Count == 0)
        {
            RowIndexFlag1 = RowIndex - 1;
            return;
        }
        //列表行
        IRow titleRow = sheet.CreateRow(RowIndex);
        for (int c = 1; c < 4; c++)
        {
            ICell cell = titleRow.CreateCell(c);
            cell.CellStyle = cellStyleTitle;

        }
        titleRow.Cells[0].SetCellValue("分销数据");
        titleRow.Cells[1].SetCellValue("指标明细");
        titleRow.Cells[2].SetCellValue("采集结果");

        RowIndex++;
        String last_cpr_name = "";
        for (int i = 0; i < dt_CPR.Rows.Count; i++)
        {
            String cpr_name = dt_CPR.Rows[i]["NAME"].ToString();
            if (cpr_name != last_cpr_name)
            {
                IEnumerable<DataRow> query =
                from temp in dt_CPR.AsEnumerable()
                where temp.Field<String>("NAME").ToString() == cpr_name
                select temp;

                DataTable dt = query.CopyToDataTable();
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(RowIndex, RowIndex + dt.Rows.Count - 1, 1, 1));//合并
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    IRow row = sheet.CreateRow(RowIndex);
                    for (int c = 2; c < 4; c++)
                    {
                        ICell cell = row.CreateCell(c);
                        cell.CellStyle = cellStyle;

                    }
                    row.Cells[0].SetCellValue(dt.Rows[j]["MEASUREMENTNAME"].ToString());

                    //图片
                    if ("6" == dt.Rows[j]["INPUTTYPE"].ToString().Trim() && !String.IsNullOrEmpty(dt.Rows[j]["PHOTO_ID"].ToString().Trim()))
                    {
                        var photo = new PhotoManager().Load(dt.Rows[j]["PHOTO_ID"].ToString().Trim());
                        String FilePath = "";
                        if (photo != null)
                        {
                            FilePath = ImagePath + "\\" + photo.PHOTOPATH;


                        }
                        if (File.Exists(FilePath))
                        {
                            row.Height = 20 * 256;

                            //add picture data to this workbook.  

                            byte[] bytes = System.IO.File.ReadAllBytes(FilePath);
                            if (bytes.Length != 0)
                            {
                                int pictureIdx = hssfworkbook.AddPicture(bytes, PictureType.JPEG);
                                //create sheet ISheet sheet = hssfworkbook.CreateSheet("Sheet1");   
                                // Create the drawing patriarch.  This is the top level container for all shapes.  
                                HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
                                //add a picture  
                                HSSFClientAnchor anchor = new HSSFClientAnchor(10, 10, 1023, 0, 3, RowIndex, 1, 3);
                                patriarch.CreatePicture(anchor, pictureIdx).Resize();
                            }

                        }
                    }
                    else
                    {
                        row.Cells[1].SetCellValue(dt.Rows[j]["VALUE"].ToString());
                    }
                    ICell cell1 = row.CreateCell(1);
                    cell1.CellStyle = cellStyle1;
                    cell1.SetCellValue(dt.Rows[j]["BRAND_NAME"].ToString() + "/" + dt.Rows[j]["NAME"].ToString());
                    RowIndex++;
                }
            }
            last_cpr_name = cpr_name;
        }

    }

    #endregion

}

class MyTable
{
    private string width;
    private string cellType;
    private string autoMerged;

    public string Width
    {
        get
        {
            if (width == "")
                width = "10";
            return width;
        }
        set
        {
            width = value;
            if (width == "")
            {
                width = "10";
            }
        }
    }


    public string AutoMerged
    {
        get { return autoMerged; }
        set { autoMerged = value; }
    }


    public string CellType
    {
        get { return cellType; }
        set { cellType = value; }
    }



}